/* a_fig1.do ******************************************************************************

This code produces figure 1

*******************************************************************************/

set more off
local stu_enroll = 1

local fyr 2001
local lyr 2017
local i = 1
matrix stu_sch_ext = J(20,3,.)
matrix colnames stu_sch_ext = year num_stu num_sch

use "$raw_data_bps/Enrollment/Enrollment98to04/EconomistStudentExtract.dta", clear
gen year = asnyear - 60000 - 1

preserve
  drop if sch == 4840 | sch == 4850
  keep if grade == "K1"
  collapse (count) enrollment = studentno, by(sch year)
  *replace enrollment = -1 if enrollment < 15
  reshape wide enrollment, i(sch) j(year)
  tempfile enrollment_98
  save `enrollment_98'
restore

tempfile 98_to_04
save `98_to_04'

forvalues y = 1997/2003 {
	preserve
	drop if sch == 4840 | sch == 4850
	keep if year == `y' & grade == "K1"


	matrix stu_sch_ext[`i',1] = `y'

	unique studentno
	matrix stu_sch_ext[`i',2] = r(sum)

	unique sch
	matrix stu_sch_ext[`i',3] = r(sum)

	local i = `i' + 1

	di "`y'"
	restore

}

use "$stata_data/enroll05_12.dta", clear
replace grade = upper(grade)
rename StudentNo studentno


preserve
  rename school sch
  drop if sch == 4840 | sch == 4850
  keep if grade == "K1"
  collapse (count) enrollment = studentno, by(sch year)
  reshape wide enrollment, i(sch) j(year)
  tempfile enrollment_05
  save `enrollment_05'
restore


tempfile 04_on
save `04_on'
forvalues y = 2004/2011 {
	preserve

	drop if sch == 4840 | sch == 4850
	keep if year == `y' & grade == "K1"


	matrix stu_sch_ext[`i',1] = `y'

	unique studentno
	matrix stu_sch_ext[`i',2] = r(sum)

	unique school
	matrix stu_sch_ext[`i',3] = r(sum)

	local i = `i' + 1
	restore

}
use `04_on',clear
append using `98_to_04', force

drop if sch == 4840 | sch == 4850
keep if grade == "K1"

unique studentno
matrix stu_sch_ext[`i',2] = r(sum)

unique school
matrix stu_sch_ext[`i',3] = r(sum)

* This output should be coppied over to the tab "F1_enroll_over_time" in the final deck
putexcel set "$results/raw_tabs/fig_1_counts.xlsx", replace
putexcel A1 = matrix(stu_sch_ext)
